package com.clouds.wms.dao;

import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import com.clouds.wms.bean.Cmsmi;
import com.clouds.wms.exception.DatabaseException;
import com.clouds.wms.util.Function;
import com.clouds.wms.util.PagesBean;
import com.clouds.wms.util.SysParams;
@Repository("cmsmiDao")
public class CmsmiDaoImpl implements CmsmiDao {
	
	private Log log = LogFactory.getLog(getClass());
	
	@Resource(name="namedParameterJdbcTemplate")
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	
	@Override
	public PagesBean getCmsmiListByMhaa(String mhaa,String miaa,int miad, int offset, int pageSize) throws DatabaseException {
		PagesBean pb = new PagesBean();
		List<Cmsmi> list = new ArrayList<Cmsmi>();
		boolean hasWhere = false;
		StringBuffer sql = new StringBuffer("SELECT miaa,miab,mh.mhab miab_str,miac,miad,miae FROM cmsmi mi LEFT JOIN cmsmh mh ON mh.mhaa=mi.miab ");
		if(mhaa != null && !"".equals(mhaa)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " miab LIKE '%" + Function.formatSQLString(mhaa) + "%'");
		}
		if(miaa != null && !"".equals(miaa)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " miaa LIKE '%" + Function.formatSQLString(miaa) + "%'");
		}
		if(miad != 0 ) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " miad = " + miad + "");
		}
		sql.append(" ORDER BY miaa DESC");
		try {
			int totalCount = namedParameterJdbcTemplate.getJdbcOperations().queryForInt(Function.toCountSql(sql.toString()));
			if(totalCount!=0){
				list = namedParameterJdbcTemplate.getJdbcOperations().query(Function.mySqlQuery(sql.toString(),offset,pageSize,totalCount),new BeanPropertyRowMapper<Cmsmi>(Cmsmi.class));
			}
			pb.setCount(totalCount);
			pb.setData(list);
			return pb;
		} catch (EmptyResultDataAccessException e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	
	@Override
	public Cmsmi getCmsmiByMiaa(String miaa) throws DatabaseException {
		String sql = "SELECT miaa,miab,mh.mhab miab_str,miac,miad,miae FROM cmsmi mi LEFT JOIN cmsmh mh ON mh.mhaa=mi.miab WHERE miaa=? ";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[]{miaa}, new BeanPropertyRowMapper<Cmsmi>(Cmsmi.class));
		} catch (EmptyResultDataAccessException e) {
			log.info("没有数据");
			return null;
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int saveCmsmi(Cmsmi cmsmi) throws DatabaseException {
		String sql = "INSERT INTO cmsmi (miaa,miab,miac,miad,miae) VALUES (:miaa,:miab,:miac,:miad,:miae)";
		try {
			SqlParameterSource param = new BeanPropertySqlParameterSource(cmsmi);
			return namedParameterJdbcTemplate.update(sql, param);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int updateCmsmi(Cmsmi cmsmi) throws DatabaseException {
		String sql = "UPDATE cmsmi SET miac=:miac,miad=:miad,miae=:miae WHERE miaa =:miaa";
		try {
			SqlParameterSource param = new BeanPropertySqlParameterSource(cmsmi);
			return namedParameterJdbcTemplate.update(sql, param);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int updateCmsmiMiag(String miaa, int miad) throws DatabaseException {
		String sql = "UPDATE cmsmi SET miad=? WHERE miaa=?";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().update(sql, miad, miaa);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public List<Cmsmi> getAllCmsmiList(String mhaa, int miag, String ecaa, String ecad, String ecae) throws DatabaseException {
		String sql = "SELECT miaa,miab,mh.mhab miab_str,miac,miad,miae,miaf,miag,miah FROM cmsmi mi LEFT JOIN cmsmh mh ON mh.mhaa=mi.miab WHERE miab=? AND miag=? AND miaa IN (SELECT ecac FROM invec WHERE ecaa=? AND ecad=? AND ecae=? AND ecaf!=0)";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().query(sql, new Object[]{mhaa, miag, ecaa, ecad, ecae}, new BeanPropertyRowMapper<Cmsmi>(Cmsmi.class));
		} catch (EmptyResultDataAccessException e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public List<Cmsmi> getCmsmiListByMiaa(String miaa) throws DatabaseException {
		String sql = "SELECT * FROM cmsmi WHERE miab=? and miag = ? ";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().query(sql, new Object[]{miaa, SysParams.STATUS_NORMAL}, new BeanPropertyRowMapper<Cmsmi>(Cmsmi.class));
		} catch (EmptyResultDataAccessException e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
}
